/******************************************************
 *                                                     
 *              Score System               
 *                                                     
 *******************************************************/

/**
 * ExcelProcess.java
 *
 * 对Excel的操作
 *
 * @author sundy
 * @version 0.0.1
 * lastModified 2011-01-01
 */

package com.sundy.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.sundy.core.Common;
import com.sundy.core.Configuration;
import com.sundy.core.Context;
import com.sundy.core.Utility;
import com.sundy.daosupport.DescriptTblDAO;
import com.sundy.daosupport.StudentTblDAO;
import com.sundy.daosupport.Subject2descDAO;
import com.sundy.daosupport.SubjectTblDAO;
import com.sundy.service.DescriptTbl;
import com.sundy.service.StudentTbl;
import com.sundy.service.Subject2desc;
import com.sundy.service.SubjectTbl;

public class ExcelProcess {
	private static Logger logger = Logger.getLogger(ExcelProcess.class);

	public static int addSubject(String name) {
		SubjectTblDAO dao = SubjectTblDAO
				.getFromApplicationContext(Common.applicationContext);
		List<SubjectTbl> list = dao.findByCaption(name);
		if (list == null || list.isEmpty()) {
			SubjectTbl subject = new SubjectTbl(name, 0);
			dao.save(subject);
			dao.getHibernateTemplate().flush();
			return subject.getId();
		} else
			return list.get(0).getId();
	}

	private static String[] subjectTypeTex = new String[] { "理", "文", "综合"};

	@SuppressWarnings("deprecation")
	private static String getRow(HSSFRow row, int index) {
		HSSFCell cell = row.getCell((short) index);
		if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
			return String.valueOf(cell.getNumericCellValue());
		else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
			return cell.getStringCellValue();
		else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
			return "";
		else
			return "";
	}

	public static boolean Process(Context context, String excel, String name,
			int subjectType) {
		HSSFWorkbook wkb;
		HSSFSheet sheet;
		try {
			FileInputStream fis = new FileInputStream(excel);
			POIFSFileSystem pss = new POIFSFileSystem(fis);
			fis.close();
			wkb = new HSSFWorkbook(pss);
			sheet = wkb.getSheetAt(0);
			DescriptTblDAO dao = DescriptTblDAO
					.getFromApplicationContext(Common.applicationContext);
			DescriptTbl item = new DescriptTbl(name, new Time((new Date())
					.getTime()), context.getCurrentUser().getId(), 0,
					subjectType);
			dao.save(item);
			HSSFRow toprow = sheet.getRow(0);
			Subject2descDAO dao2 = Subject2descDAO
					.getFromApplicationContext(Common.applicationContext);
			for (short i = 5; i < 15; i++) {
				String column = getRow(toprow, i);
				column = column.trim();
				if (column.equals("总分"))
					break;
				int id = addSubject(column + "-" + subjectTypeTex[subjectType]);
				Subject2desc sub2desc = new Subject2desc(id, item.getId());
				dao2.save(sub2desc);
			}
			dao2.getHibernateTemplate().flush();
			StudentTblDAO studao = StudentTblDAO
					.getFromApplicationContext(Common.applicationContext);
			int j = 1;
			while (true) {
				HSSFRow row = sheet.getRow(j);
				if (row == null || row.getCell((short) 0) == null)
					break;
				String root = getRow(row, 0);
				String _class = getRow(row, 1);
				String usr = getRow(row, 2);
				String number = getRow(row, 3);
				String ex_room = getRow(row, 4);
				StudentTbl student = new StudentTbl(usr, _class, number, root,
						ex_room, 0, item.getId());
				studao.save(student);
				j++;
			}
			studao.getHibernateTemplate().flush();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@SuppressWarnings("deprecation")
	public static void export(int id) {
		StudentTblDAO dao = StudentTblDAO
				.getFromApplicationContext(Common.applicationContext);
		List<StudentTbl> list = dao.findByDescId(id);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-hh-mm-ss");
		String excel = Configuration.getProperty("/config/export-dir")
				+ File.separator + "export-" + sdf.format(new Date()) + ".xls";
		HSSFWorkbook wkb;
		logger.info(excel);
		HSSFSheet sheet;
		try {
			FileOutputStream fos = new FileOutputStream(excel);
			wkb = new HSSFWorkbook();
			sheet = wkb.createSheet();
			HSSFRow toprow = sheet.createRow(0);
			toprow.createCell((short) 0).setCellValue("考场");
			toprow.createCell((short) 1).setCellValue("班级");
			toprow.createCell((short) 2).setCellValue("姓名");
			toprow.createCell((short) 3).setCellValue("考号");
			toprow.createCell((short) 4).setCellValue("考场地点");
			Subject2descDAO dao2 = Subject2descDAO
					.getFromApplicationContext(Common.applicationContext);
			List<Subject2desc> list2 = dao2.findByDescId(id);
			int last = 5 + list2.size();
			for (int i = 0; i < list2.size(); i++) {
				toprow.createCell((short) (5 + i)).setCellValue(
						Utility.getSubjectById(list2.get(i).getSubjectId()));
			}
			toprow.createCell((short) last).setCellValue("总分");
			for (int i = 0; i < list.size(); i++) {
				StudentTbl curr = list.get(i);
				HSSFRow row = sheet.createRow(i + 1);
				row.createCell((short) 0).setCellValue(
						WebUtility.getNondotString(curr.getAddress()));
				row.createCell((short) 1).setCellValue(
						WebUtility.getNondotString(curr.getClass_()));
				row.createCell((short) 2).setCellValue(curr.getName());
				row.createCell((short) 3).setCellValue(
						WebUtility.getNondotString(curr.getNumber()));
				row.createCell((short) 4).setCellValue(
						WebUtility.getNondotString(curr.getExamRoom()));
				int total = 0;
				for (int j = 0; j < list2.size(); j++) {
					int ij = Utility.getScore(curr, list2.get(j));
					row.createCell((short) (5 + j)).setCellValue(ij);
					total += ij;
				}
				row.createCell((short) last).setCellValue(total);
			}
			wkb.write(fos);
			fos.flush();
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static String exportToHtml(int id) {
		StudentTblDAO dao = StudentTblDAO
				.getFromApplicationContext(Common.applicationContext);
		DescriptTblDAO ddao = DescriptTblDAO
				.getFromApplicationContext(Common.applicationContext);
		DescriptTbl desc = ddao.findById(id);
		String title = "";
		if (desc != null)
			title = desc.getCaption();
		List<StudentTbl> list = dao.findByDescId(id);
		if (list == null || list.isEmpty()) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		sb.append("<div style='text-align: center; width: 100%;'><h2>").append(
				title).append("</h2></div>");
		sb
				.append("<table style='border: 1px solid #cccccc; width: 100%;'><tr>");
		sb.append("<td>序号</td>");
		sb.append("<td>考场</td>");
		sb.append("<td>班级</td>");
		sb.append("<td>姓名</td>");
		sb.append("<td>考号</td>");
		sb.append("<td>考场地点</td>");
		Subject2descDAO dao2 = Subject2descDAO
				.getFromApplicationContext(Common.applicationContext);
		List<Subject2desc> list2 = dao2.findByDescId(id);
		for (int i = 0; i < list2.size(); i++) {
			sb.append("<td>").append(
					Utility.getSubjectById(list2.get(i).getSubjectId()))
					.append("</td>");
		}
		sb.append("<td>总分</td></tr>");
		for (int i = 0; i < list.size(); i++) {
			StudentTbl curr = list.get(i);
			if (i % 2 == 0)
				sb.append("<tr>");
			else
				sb.append("<tr style='background-color: #eeeeee'>");
			sb.append("<td>").append(i + 1).append("</td>");
			sb.append("<td>").append(
					WebUtility.getNondotString(curr.getAddress())).append(
					"</td>");
			sb.append("<td>").append(
					WebUtility.getNondotString(curr.getClass_())).append(
					"</td>");
			sb.append("<td>")
					.append(WebUtility.getNondotString(curr.getName())).append(
							"</td>");
			sb.append("<td>").append(
					WebUtility.getNondotString(curr.getNumber())).append(
					"</td>");
			sb.append("<td>").append(
					WebUtility.getNondotString(curr.getExamRoom())).append(
					"</td>");
			int total = 0;
			for (int j = 0; j < list2.size(); j++) {
				int ij = Utility.getScore(curr, list2.get(j));
				if (ij == 0 || ij >= 150) {
					sb.append("<td><font color='red'>").append(ij).append(
							"</font></td>");
				} else
					sb.append("<td>").append(ij).append("</td>");
				total += ij;
			}
			sb.append("<td>").append(total).append("</td>").append("</tr>");
		}
		sb.append("</table>");
		return sb.toString();
	}

	public static void main(String[] args) {
		export(6);
	}
}
